/* Dwarf_Schema.sql */

---------------------------------------------------------------------------------------
create table ejb_dwarf
(
	dwarf_id	int				primary key,
	dwarf_name	varchar(80)	unique		not null,
	born		int				not null,
	home_id		int				null,		/* mountain_id */
	spouse_id	int				null		/* dwarf_id */
);

alter table ejb_dwarf add
	constraint ejb_dwarf_spouse_fk foreign key (spouse_id)
	references ejb_dwarf (dwarf_id);

grant all on ejb_dwarf to public;
\g

create sequence ejb_dwarf_dwarf_id_seq; 
\g

grant all on ejb_dwarf_dwarf_id_seq to public;
\g

---------------------------------------------------------------------------------------
create table ejb_mountain
(
	mountain_id	int				primary key,
	mountain_name	varchar(80)	unique		not null,
	location	varchar(80)			not null,
	king_id		int				null		/* dwarf_id */
);

alter table ejb_dwarf add
	constraint ejb_dwarf_home_fk foreign key (home_id)
	references ejb_mountain (mountain_id);

alter table ejb_mountain add
	constraint ejb_mountain_king_fk foreign key (king_id)
	references ejb_dwarf (dwarf_id);

grant all on ejb_mountain to public;
\g

create sequence ejb_mountain_mountain_id_seq; 
\g

grant all on ejb_mountain_mountain_id_seq to public;
\g